package veterinaria.dao;

import java.io.Console;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import veterinaria.excepcion.DAOExcepcion;
import veterinaria.modelo.Comprobante;
import veterinaria.modelo.ComprobanteDetalle;
import veterinaria.modelo.Dueno;
import veterinaria.negocio.GestionDueno;
import veterinaria.util.ConexionBD;

public class ComprobanteDAO  extends BaseDAO {
	
	public Comprobante insertar(Comprobante vo) throws DAOExcepcion {
		String query = "insert into sComprobante(fecha,tipo,estado,idcliente) values (?,?,?,?)";
		String query2= null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//Insertar
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setDate(1, vo.getFechaRegistro());
			stmt.setString(2, vo.getTipo());
			stmt.setInt(3, vo.getEstado());
			stmt.setInt(4, vo.getCliente().getIdDueno());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}


			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setIdComprobante(id);
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public ComprobanteDetalle insertarDetalle(ComprobanteDetalle vo) throws DAOExcepcion {
		String query = "insert into detalleComprobante(idSComprobante,cantidad,idservicio,idMedicamento) values (?,?,?,?)";
		String query2= null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//Insertar
			
			
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setInt(1, vo.getIdComprobante());
			stmt.setInt(2, vo.getCantidad());
			stmt.setString(3, null);
			stmt.setString(4, Integer.toString(vo.getMedicamento().getCodigo()));
						
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}

			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setIdDetalle(id);
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return vo;
	}

	public Collection<Comprobante> buscarPorNombre(int comprobante,String cliente, String f1, String f2)
			throws DAOExcepcion {
		String query = "SELECT scomprobante.idComprobante, scomprobante.fecha, scomprobante.tipo, scomprobante.idCliente, scomprobante.estado, cliente.nombre, cliente.paterno, cliente.materno FROM scomprobante INNER JOIN cliente ON scomprobante.idCliente = cliente.idCliente   ";
		
		
		if (comprobante==0){
			
			if (cliente!=""){
			
			query = query + " where concat(nombre ,' ',paterno ,' ', materno) like '%"+String.valueOf(cliente)+"%' ";
			
			
					if ((f1!="")&&(f2!="")){
						
						query = query + " and fecha BETWEEN '"+f1+"'  and '" +f2+"' ";
		
					}
					
			
			
			}else{
				
				if ((f1!="")&&(f2!="")){
					
					query = query + " where fecha BETWEEN '"+f1+"'  and '" +f2+"' ";

				}
				
			}
			
		}else{
			
			query = query + " where idComprobante="+ String.valueOf(comprobante);

				if (cliente!=""){
				
					query = query + " and concat(nombre ,' ',paterno ,' ', materno) like '%"+String.valueOf(cliente)+"%' ";
				
				
						if ((f1!="")&&(f2!="")){
					
							query = query + " and fecha BETWEEN '"+f1+"'  and '" +f2+"' ";

						}

				}else{
				

					if ((f1!="")&&(f2!="")){
						
						query = query + " where fecha BETWEEN '"+f1+"'  and '" +f2+"' ";

					}

					
					
				}
		}
		
		
	
/*
		
			query = query + "idCliente=?";
		if ("NOMBRE".equals(atributo))
			query = query + "concat(nombre ,' ',paterno ,' ', materno)=?";
		if ("DOCUMENTO".equals(atributo))
			query = query + "rucdni=?";
		
	*/	
		
		
		
		
		
		
		Collection<Comprobante> lista = new ArrayList<Comprobante>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			//stmt.setString(1,"%" + cliente + "%" );
			
			System.out.println(query);

			rs = stmt.executeQuery();
			DuenoDAO d=new DuenoDAO();
				Dueno d1=new Dueno();
				
			while (rs.next()) {
				Comprobante vo = new Comprobante();
				
				vo.setIdComprobante(rs.getInt("idComprobante"));				
				vo.setFechaRegistro(rs.getDate("fecha"));
				vo.setTipo(rs.getString("tipo"));
				
				
				d1=d.obtener("CODIGO", rs.getInt("idCliente"));
				
			vo.setCliente(d1);
				
				vo.setEstado(rs.getInt("estado"));
				
				
				
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	
}
